package com.zyit.util;




import org.apache.commons.dbcp.BasicDataSource;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class DBHelp {


    private static BasicDataSource dataSource = null;

    static {
        try {
            InputStream input = DBHelp.class.getClassLoader().getResourceAsStream("dp.properties");
            Properties properties = new Properties();
            properties.load(input);

            String DRIVER = (String) properties.get("db.driver");
            String URL = (String) properties.get("db.url");
            String USERNAME = (String) properties.get("db.username");
            String PWD = (String) properties.get("db.pwd");


            dataSource = new BasicDataSource();
            dataSource.setDriverClassName(DRIVER);
            dataSource.setUrl(URL);
            dataSource.setUsername(USERNAME);
            dataSource.setPassword(PWD);
            //当这个池被启动时初始化的创建的连接个数，起始生效版本：1.2
            dataSource.setInitialSize(5);
            //可以在这个池中同时被分配的有效连接数的最大值，如设置为负数，则不限制
            dataSource.setMaxActive(20);
            //可以在池中保持空闲的最大连接数，超出设置值之外的空闲连接将被回收，如设置为负数，则不限制
            dataSource.setMaxIdle(15);
            //可以在池中保持空闲的最小连接数，超出设置值之外的空闲连接将被创建，如设置为0，则不创建
            dataSource.setMinIdle(10);
            //（如果没有可用连接）池在抛出异常前等待的一个连接被归还的最大毫秒数，设置为-1则等待时间不确定
            dataSource.setMaxWait(5000);
            input.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 查询多条记录
     *
     * @param sql
     * @param rowMapper
     * @param args
     * @return
     */

    public <T> List<T> executeQueryForList(String sql, RowMapper<T> rowMapper, Object... args) {

        System.out.println("sql:" + sql);
        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;

        List<T> list = new ArrayList();
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                stat.setObject(i + 1, args[i]);
            }

            rs = stat.executeQuery();

            while (rs.next()) {
                T obj = rowMapper.mapperRow(rs);
                list.add(obj);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(rs, stat, conn);
        }

        return list;


    }


    /**
     * 查询单条记录
     *
     * @param sql
     * @param rowMapper
     * @param args
     * @return
     */
    public <T> T executeQueryForObject(String sql, RowMapper<T> rowMapper, Object... args) {

        Connection conn = null;
        PreparedStatement stat = null;
        ResultSet rs = null;
        System.out.println("sql : " + sql);
        T obj = null;
        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                stat.setObject(i + 1, args[i]);
            }

            rs = stat.executeQuery();

            if (rs.next()) {
                obj = rowMapper.mapperRow(rs);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(rs, stat, conn);
        }

        return obj;

    }


    /**
     * 增删改
     *
     * @param sql
     * @param args
     * @return
     */
    public int executeUpdate(String sql, Object... args) {

        Connection conn = null;
        PreparedStatement stat = null;

        System.out.println("sql : " + sql);
        int rows = -1;

        try {
            conn = getConnection();
            stat = conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                stat.setObject(i + 1, args[i]);
            }

            rows = stat.executeUpdate();


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(null, stat, conn);
        }
        return rows;
    }


    /**
     * 获取链接
     *
     * @return
     * @throws Exception
     */
    public Connection getConnection() throws Exception {

        return dataSource.getConnection();
    }


    /**
     * 释放资源
     *
     * @param rs
     * @param stat
     * @param conn
     */
    public void close(ResultSet rs, PreparedStatement stat, Connection conn) {


        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


}

